package com.futvan.z.system.zdb;
import java.io.IOException;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map.Entry;

import javax.servlet.http.HttpServletRequest;

import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.core.io.DefaultResourceLoader;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.stereotype.Service;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

import com.futvan.z.framework.common.bean.Code;
import com.futvan.z.framework.common.bean.Result;
import com.futvan.z.framework.core.SuperService;
import com.futvan.z.framework.core.z;
import com.futvan.z.framework.util.BeanUtil;
import com.futvan.z.framework.util.DBUtil;
import com.futvan.z.system.zform.z_form_table;
import com.futvan.z.system.zform.z_form_table_column;
@Service
public class ZdbService extends SuperService{


	/**
	 * 添加核心库到数据源表
	 */
	public Result AddZdbToDBS() {
		Result result = new Result();
		//添加核心库到数据源记录中。供数据管理相关功能使用
		List<z_db_table> tList = sqlSession.selectList("z_db_table_select_pid", "z");
		for (z_db_table t : tList) {
			sqlSession.delete("delete", "delete from z_db_table_column where pid = '"+t.getZid()+"'");
		}
		sqlSession.delete("delete", "delete from z_db_table where pid = 'z'");
		sqlSession.delete("delete", "delete from z_db where zid = 'z'");

		z_db zdb = new z_db();
		zdb.setZid("z");
		zdb.setDbid("z");
		zdb.setName("平台核心库");
		zdb.setDriverClassName("*********");
		zdb.setUsername("*********");
		zdb.setPassword("*********");
		zdb.setMaxWaitMillis("9999");
		zdb.setDbip("*********");
		zdb.setDb_port("9999");
		zdb.setDb_name("*********");
		zdb.setDb_type("*********");
		sqlSession.insert("z_db_insert", zdb);

		for (Entry<String, z_form_table> EntryTable : z.tables.entrySet()) {
			z_form_table t = EntryTable.getValue();
			z_db_table dbtable = new z_db_table();
			String tid = z.newZid("z_db_table");
			dbtable.setZid(tid);
			dbtable.setPid("z");
			dbtable.setTable_id(t.getTable_id());
			dbtable.setTable_title(t.getTable_title());
			sqlSession.insert("z_db_table_insert", dbtable);

			List<z_form_table_column> columnlist = t.getZ_form_table_column_list();
			for (z_form_table_column tcolumn : columnlist) {
				z_db_table_column tc = new z_db_table_column();
				tc.setZid(z.newZid("z_db_table_column"));
				tc.setPid(tid);
				tc.setColumn_id(tcolumn.getColumn_id());
				tc.setColumn_name(tcolumn.getColumn_name());
				tc.setColumn_type(tcolumn.getColumn_type());
				tc.setColumn_length(tcolumn.getColumn_length_db());
				sqlSession.insert("z_db_table_column_insert", tc);
			}

		}

		result.setCode(Code.SUCCESS);
		result.setMsg("更新数据源核心库信息成功，共计 "+z.tables.size()+"张表");
		return result;

	}


	/**
	 * 保存
	 * @param bean
	 * @param request
	 * @return
	 * @throws Exception
	 */
	public @ResponseBody Result z_db_save_button(@RequestParam HashMap<String,String> bean,HttpServletRequest request) throws Exception {
		Result result = new Result();
		String db_type = bean.get("db_type");
		String dbip = bean.get("dbip");
		String db_port = bean.get("db_port");
		String db_name = bean.get("db_name");
		String PageType = bean.get("PageType");
		String url_extend = bean.get("url_extend");
		if(z.isNotNull(db_type)) {
			if(z.isNotNull(dbip)) {
				if(z.isNotNull(db_port)) {
					if(z.isNotNull(db_name)) {
						if(z.isNotNull(PageType)) {
							if("add".equals(bean.get("PageType"))) {
								bean.put("zid", bean.get("dbid"));
								if(z.isNull(url_extend)) {
									url_extend = "";
								}else {
									url_extend = "?"+url_extend;
								}
								String url = "";
								if("mysql".equals(db_type)) {
									url = "jdbc:"+db_type+"://"+dbip+":"+db_port+"/"+db_name+url_extend;
								}
								if("oracle".equals(db_type)) {
									url = "jdbc:"+db_type+":thin:@"+dbip+":"+db_port+"/"+db_name+url_extend;
								}
								if("sqlserver".equals(db_type)) {
									url = "jdbc:sqlserver://"+dbip+":"+db_port+";DatabaseName="+db_name+url_extend;
								}
								bean.put("url", url);

								//创建SqlSession
								z_db db = BeanUtil.MapToBean(bean, z_db.class);
								boolean rsqlsession = DBUtil.CreateSqlSessionTemplate(db);
								if(!rsqlsession) {
									z.Exception("z_db_save_button error | 创建SqlSession出错");
								}
								result = insert(bean, request);
							}else if("edit".equals(bean.get("PageType"))) {
								if(z.isNull(url_extend)) {
									url_extend = "";
								}else {
									url_extend = "?"+url_extend;
								}
								String url = "";
								if("mysql".equals(db_type)) {
									url = "jdbc:"+db_type+"://"+dbip+":"+db_port+"/"+db_name+url_extend;
								}
								if("oracle".equals(db_type)) {
									url = "jdbc:"+db_type+":thin:@"+dbip+":"+db_port+"/"+db_name+url_extend;
								}
								if("sqlserver".equals(db_type)) {
									url = "jdbc:sqlserver://"+dbip+":"+db_port+";DatabaseName="+db_name+url_extend;
								}
								bean.put("url", url);

								//创建SqlSession
								z_db db = BeanUtil.MapToBean(bean, z_db.class);
								boolean rsqlsession = DBUtil.CreateSqlSessionTemplate(db);
								if(!rsqlsession) {
									z.Exception("z_db_save_button error | 创建SqlSession出错");
								}
								result = update(bean, request);
							}else {
								z.Exception("z_db_save_button error | 页面类型参数不正确："+bean.get("PageType"));
							}
						}else {
							result.setCode(Code.ERROR);
							result.setMsg("页面编辑类型不能为空");
						}
					}else {
						result.setCode(Code.ERROR);
						result.setMsg("数据库连接实例名称不能为空");
					}
				}else {
					result.setCode(Code.ERROR);
					result.setMsg("数据库连接端口不能为空");
				}
			}else {
				result.setCode(Code.ERROR);
				result.setMsg("数据库连接地址不能为空");
			}
		}else {
			result.setCode(Code.ERROR);
			result.setMsg("数据库类型不能为空");
		}

		//如果保存成功，执行更新表与字段信息操作
		if(Code.SUCCESS.equals(result.getCode())) {
			result = RefreshDBColumn(bean);
			result.setData(bean.get("zid"));
		}
		return result;
	}

	/**
	 * 刷新表字段信息
	 * @param bean
	 * @return
	 * @throws Exception
	 */
	public Result RefreshDBColumn(@RequestParam HashMap<String,String> bean) throws Exception {
		Result result = new Result();
		String db_type = bean.get("db_type");
		String dbip = bean.get("dbip");
		String db_port = bean.get("db_port");
		String db_name = bean.get("db_name");
//		String PageType = bean.get("PageType");
//		String url_extend = bean.get("url_extend");
		if(z.isNotNull(db_type)) {
			if(z.isNotNull(dbip)) {
				if(z.isNotNull(db_port)) {
					if(z.isNotNull(db_name)) {
						//判读数据库是否可以连接
						z_db db = BeanUtil.MapToBean(bean, z_db.class);
						if(z.isNotNull(db)) {
							//获取所有表名和字段名信息
							List<z_db_table> tables = DBUtil.getTables(db);

							//清空旧数据
							List<z_db_table> old_tables = selectList("z_db_table_select_pid", db.getZid());
							for (z_db_table otable : old_tables) {
								delete("delete from z_db_table_column where pid = '"+otable.getZid()+"'");
							}
							delete("delete from z_db_table where pid = '"+db.getZid()+"'");

							//下一步，将获取的表信息与字段信息，保存到数据库子表中
							for (z_db_table table : tables) {

								//保存所有字段
								List<z_db_table_column> columnList = table.getZ_db_table_column_list();
								for (z_db_table_column column : columnList) {
									int num = insert("z_db_table_column_insert", column);
									if(num!=1) {
										z.Exception("保存表字段出错");
									}
								}

								//保存表信息
								int num = insert("z_db_table_insert", table);
								if(num!=1) {
									z.Exception("保存表信息出错");
								}

							}
							//返回信息
							result.setCode(Code.SUCCESS);
							result.setMsg("保存数据源成功，成功获取表"+tables.size()+"张");
						}else {
							result.setCode(Code.ERROR);
							result.setMsg("参数无效，无法转换成z_db对象");
						}
					}else {
						result.setCode(Code.ERROR);
						result.setMsg("数据库连接实例名称不能为空");
					}
				}else {
					result.setCode(Code.ERROR);
					result.setMsg("数据库连接端口不能为空");
				}
			}else {
				result.setCode(Code.ERROR);
				result.setMsg("数据库连接地址不能为空");
			}
		}else {
			result.setCode(Code.ERROR);
			result.setMsg("数据库类型不能为空");
		}
		return result;
	}
}
